/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package ortustve.db;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Properties;
import org.apache.derby.drda.NetworkServerControl;

/**
 *
 * @author jphipps
 */
public class derbyengine extends ortustve.ortusvars implements IEngine {
    private DBAccess dba = new DBAccess();
    private String embeddeddriver = "org.apache.derby.jdbc.EmbeddedDriver";
    private String netdriver = "org.apache.derby.jdbc.ClientDriver";
    private String derbyprotocol = ortustve.api.GetProperty("derbyprotocol", "jdbc:derby:ortusDB");
    private Connection gconn = null;
    public boolean indexMediaRunning = false;

    public derbyengine () {

        if ( ! connectDB(derbyprotocol,"sage","sage") )
            return;

        Runtime.getRuntime().addShutdownHook(new Thread() {
           public void run() {
               ShutdownDatabase();
           }
        });
    }

    public void ShutdownDatabase() {
        ortustve.api.DebugLog(INFO, "Shuting down ortusDB connection");
        try {
             gconn.close();
             DriverManager.getConnection("jdbc:derby:;shutdown=true");
        } catch (Exception ex ) {}
    }

    private boolean connectDB (String connectinfo, String user, String passwd) {
        ortustve.api.DebugLog(INFO,"Loading ortusdb");
        if ( ! loadDriver()) {
            ortustve.api.DebugLog(INFO, "derby jar files are missing");
            return false;
        }

        try {
           Properties props = new Properties(); // connection properties
           props.put("user", user);
           props.put("password", passwd);
           gconn = DriverManager.getConnection(connectinfo, props);
           gconn.setAutoCommit(true);
        } catch( SQLException e ) {
            ortustve.api.DebugLog(ERROR, "SQLEexception: " + e);
        }
        return true;
    }

    private boolean loadDriver() {
        String driver = null;
        try {
                driver = embeddeddriver;
                System.setProperty("derby.drda.startNetworkServer","true");
                Class.forName(driver).newInstance();
                waitForStart();
                ortustve.api.DebugLog(INFO, "ortusDB: Successfully Loaded Embedded Driver and Network Server");
        } catch (ClassNotFoundException cnfe) {
            ortustve.api.DebugLog( ERROR,"Unable to load the JDBC driver " + driver);
            ortustve.api.DebugLog( ERROR,"Please check your CLASSPATH.");
            return false;
        } catch (InstantiationException ie) {
            ortustve.api.DebugLog(ERROR,"Unable to instantiate the JDBC driver " + driver);
            return false;
        } catch (IllegalAccessException iae) {
            ortustve.api.DebugLog(ERROR,"Not allowed to access the JDBC driver " + driver);
            return false;
        }
        return true;
    }

    private static void waitForStart()
	{
         org.apache.derby.drda.NetworkServerControl server = null;
         try {
            server = new NetworkServerControl();
         } catch (Exception e) {
             ortustve.api.DebugLog(ERROR,"ortusDB: Failure " + e);
         }

	 ortustve.api.DebugLog(INFO, "ortusDB: Waiting for Network Server to come online");

 	 for (int i = 0; i < 10 ; i ++)	{
            try {
		Thread.currentThread().sleep(5000);
		server.ping();
                i=20;
            } catch (Exception e)
        	{
             	 ortustve.api.DebugLog(ERROR, "Try #" + i + " " +e.toString());
		 if (i == 9 ) {
                    ortustve.api.DebugLog(ERROR, "ortusDB: Giving up trying to connect to Network Server!");
                    return;
                 }
            }
	}
	ortustve.api.DebugLog(INFO, "ortusDB:Network Server now running");
    }
    public Connection GetConnection() {
		try {
			if ( gconn == null)
				connectDB(derbyprotocol,"sage","sage");
			else if ( gconn.isClosed())
				connectDB(derbyprotocol,"sage","sage");

			return gconn;
		} catch (Exception ex) {
			ortustve.api.DebugLog(ERROR,"SQLException: GetConnection: " + ex);
			return null;
		}
    }
    public int executeSQL(String sql) {
	  return dba.executeSQL(GetConnection(), sql);
    }
    public List<Object> executeSQLQuery(String sql) {
	  return dba.executeSQLQuery(GetConnection(), sql);
    }
    public List<Object> executeSQLQueryCache(String sql) {
	  return dba.executeSQLQueryCache(GetConnection(), sql);
    }
    public List<List> executeSQLQueryArray(String sql) {
	  return dba.executeSQLQueryArray(GetConnection(), sql);
    }
    public List<List> executeSQLQueryArrayCache(String sql) {
	  return dba.executeSQLQueryArrayCache(GetConnection(), sql);
    }
    public List<HashMap> executeSQLQueryHash(String sql) {
	  return dba.executeSQLQueryHash(GetConnection(), sql);
    }
    public List<HashMap> executeSQLQueryHashCache(String sql) {
	  return dba.executeSQLQueryHashCache(GetConnection(), sql);
    }
    public List<Object> getMediaFilesSQL(String sql) {
	  return dba.getMediaFilesSQL(GetConnection(), sql);
    }

    public boolean createDB() {
	  return dba.createDB(GetConnection());
    }

 public List<Object> GetStatus() {
	 List<Object> status = new ArrayList<Object>();

//	 List<List> result = executeSQLQueryArray("call database()");
//	 if ( result.size() > 0)
//		 status.add("Database Name: " + result.get(0).get(0));
//	 result = executeSQLQueryArray("call database_path()");
//	 if ( result.size() > 0)
//		 status.add("Database Path: " + result.get(0).get(0));
//	 result = executeSQLQueryArray("call memory_used()");
//	 if ( result.size() > 0)
//		 status.add("Memory Used: " + result.get(0).get(0));
//	 result = executeSQLQueryArray("call memory_free()");
//	 if ( result.size() > 0)
//		 status.add("Memory Free: " + result.get(0).get(0));
//	 result = executeSQLQueryArray("select count(*) from INFORMATION_SCHEMA.SESSIONS");
//	 if ( result.size() > 0)
//		 status.add("Active Sessions: " + result.get(0).get(0));
//	 result = executeSQLQueryArray("select count(*) from sage.media");
//	 if ( result.size() > 0)
//		 status.add("Total Media Objects: " + result.get(0).get(0));
//	 status.add("Procotol: " + derbyprotocol);

	 return status;
 }
    public boolean backupDB() {


       String backupdate = new java.text.SimpleDateFormat("yyyy-MM-dd:hhmm").format(new Date());
  //     File backupdir = new File(Configuration.GetProperty(UIContext.SAGETV_PROCESS_LOCAL_UI, driver, backupdate) + java.io.File.separator + "backup");

  //     if ( ! backupdir.exists() )
  //         backupdir.mkdir();

//       File thisbackup = new File(ortus.api.env.ConfigPath + java.io.File.separator + "backup" + java.io.File.separator +  backupdate);
//       if ( ! thisbackup.exists() )
//           thisbackup.mkdir();

//       ortus.api.DebugLog(INFO,"Backing up sageDB...To: " + thisbackup.getAbsolutePath());

       try {
           CallableStatement cs = gconn.prepareCall("CALL SYSCS_UTIL.SYSCS_BACKUP_DATABASE(?)");
 //          cs.setString(1, backupdir.getAbsolutePath());
           cs.execute();
           cs.close();
       } catch ( SQLException e) {
           ortustve.api.DebugLog(ERROR,"sageDB Backup failed");
           return false;
       }

       ortustve.api.DebugLog(INFO, "Backup of sageDB successful");
       return true;
    }

    public boolean restoreDB(String backupdir) {

        ortustve.api.DebugLog(INFO," ortusDB: Restore starting from backup: " + backupdir);

        ShutdownDatabase();

        if ( ! connectDB(derbyprotocol+"sageDB;restoreFrom=" + backupdir,"sage","sage") ) {
            ortustve.api.DebugLog(ERROR,"ortusDB: Restore failure");
            return false;
        }

        ortustve.api.DebugLog(INFO, "ortusDB: Restore successful");

        return true;
    }

    public void StartTcpServer() {
        return;
    }
    public void StopTcpServer() {
        return;
    }
    public boolean IsTcpServerRunning() {
        return false;
    }
    public void StartWebServer() {
        return;
    }
    public void StopWebServer() {
        return;
    }
    public boolean IsWebServerRunning() {
        return false;
    }
    public void StartPgServer() {
        return;
    }
    public void StopPgServer() {
        return;
    }
    public boolean IsPgServerRunning() {
        return false;
    }

	@Override
	public List<Object> getSageMediaFilesSQL(String sql) {
		throw new UnsupportedOperationException("Not supported yet.");
	}
}
